using System;
using System.Collections.Generic;
using System.Text;

using System.Data.Common;

using MakeIT.Framework.Data;
using MakeIT.SGI.ERP.Entidades;

namespace MakeIT.SGI.ERP.Negocios
{
	public partial class SgiDAOT : DAOT
	{

		public ClucroVO getClucro(int? idCLucro, string cLucro)
		{
			ClucroVO clucroVO = null;
			List<ClucroVO> listClucroVOs = getClucros(idCLucro, cLucro, null, null);
			if (listClucroVOs != null && listClucroVOs.Count > 0)
			{
				clucroVO = listClucroVOs[0];
			}
			return clucroVO;
		}

		public ClucroVO getClucro(int? idCLucro)
		{
			return getClucro(idCLucro, null);
		}

		public List<ClucroVO> getClucros()
		{
			return getClucros(null, null, null, null);
		}

		public List<ClucroVO> getClucros(int? idCLucro)
		{
			return getClucros(idCLucro, null, null, null);
		}

		public List<ClucroVO> getClucros(int? idCLucro, string cLucro)
		{
			return getClucros(idCLucro, cLucro, null, null);
		}

		public List<ClucroVO> getClucros(int? idCLucro, string cLucro, int? idFilial, int? idCLucroPai)
		{
			string where = "WHERE ";
			StringBuilder query = new StringBuilder();

			query.AppendLine("SELECT clc.idCLucro as clc_idCLucro, clc.cLucro as clc_cLucro, clc.idFilial as clc_idFilial, fil.filial as fil_filial, clc.idCLucroPai as clc_idCLucroPai, clc2.cLucro as clc2_cLucro, clc.cadastro as clc_cadastro, clc.cadastradoPor as clc_cadastradoPor, clc.edicao as clc_edicao, clc.editadoPor as clc_editadoPor  ");
			query.AppendLine("FROM clucro as clc LEFT OUTER JOIN filial as fil on clc.idFilial=fil.idFilial  LEFT OUTER JOIN clucro as clc2 on clc.idCLucroPai=clc2.idCLucro  ");
			if (idCLucro != null)
			{
				query.AppendLine(where + "clc.idCLucro=@idCLucro ");
				where = "AND ";
			}
			if (cLucro != null)
			{
				query.AppendLine(where + "clc.cLucro LIKE @cLucro ");
				where = "AND ";
			}
			if (idFilial != null)
			{
				query.AppendLine(where + "clc.idFilial=@idFilial ");
				where = "AND ";
			}
			if (idCLucroPai != null)
			{
				query.AppendLine(where + "clc.idCLucroPai=@idCLucroPai ");
				where = "AND ";
			}


			query.AppendLine("ORDER BY clc.cLucro ");

			DbCommand cmd = this.getCommand();
			cmd.CommandText = query.ToString();

			if (idCLucro!= null) cmd.Parameters.Add(getParameter(cmd, "@idCLucro", idCLucro));
			if (cLucro!= null) cmd.Parameters.Add(getParameter(cmd, "@cLucro", "%"+cLucro+"%"));
			if (idFilial!= null) cmd.Parameters.Add(getParameter(cmd, "@idFilial", idFilial));
			if (idCLucroPai!= null) cmd.Parameters.Add(getParameter(cmd, "@idCLucroPai", idCLucroPai));


			DbDataReader reader = null;
			try
			{
				reader = cmd.ExecuteReader();

				List<ClucroVO> listClucro = new List<ClucroVO>();

				while (reader.Read())
				{
					ClucroVO clucroVO = ClucroVO.CreateVO();
					clucroVO.idCLucro = reader.GetInt32(reader.GetOrdinal("clc_idCLucro"));
					clucroVO.cLucro = reader.GetString(reader.GetOrdinal("clc_cLucro"));
					clucroVO.idFilial = reader.GetInt32(reader.GetOrdinal("clc_idFilial"));
					if (!reader.IsDBNull(reader.GetOrdinal("fil_filial")))
						clucroVO.idFilialVO.filial = reader.GetString(reader.GetOrdinal("fil_filial"));
                    if (!reader.IsDBNull(reader.GetOrdinal("clc_idCLucroPai")) && reader.GetOrdinal("clc_idCLucroPai") != -1)
                        clucroVO.idCLucroPai = reader.GetInt32(reader.GetOrdinal("clc_idCLucroPai"));
                    else
                        clucroVO.idCLucroPai = -1;
					if (!reader.IsDBNull(reader.GetOrdinal("clc2_cLucro")))
						clucroVO.idCLucroPaiVO.cLucro = reader.GetString(reader.GetOrdinal("clc2_cLucro"));
					clucroVO.cadastro = reader.GetDateTime(reader.GetOrdinal("clc_cadastro"));
					clucroVO.cadastradoPor = reader.GetString(reader.GetOrdinal("clc_cadastradoPor"));
					clucroVO.edicao = reader.GetDateTime(reader.GetOrdinal("clc_edicao"));
					clucroVO.editadoPor = reader.GetString(reader.GetOrdinal("clc_editadoPor"));


					listClucro.Add(clucroVO);
				}
				reader.Close();
				return listClucro;
			}
			catch (Exception ex) { throw ex; }
			finally
			{
				if (reader != null && !reader.IsClosed)
					reader.Close();
			}
		}

		public void setClucro(ClucroVO clucroVO)
		{
			StringBuilder query = new StringBuilder();

			query.AppendLine("UPDATE clucro ");
			query.AppendLine("SET cLucro=@cLucro, idFilial=@idFilial, idCLucroPai=@idCLucroPai,  cadastro=@cadastro, cadastradoPor=@cadastradoPor, edicao=@edicao, editadoPor=@editadoPor ");
			query.AppendLine("WHERE idCLucro=@idCLucro ");

			DbCommand cmd = this.getCommand();
			cmd.CommandText = query.ToString();

			cmd.Parameters.Add(getParameter(cmd, "@idCLucro", clucroVO.idCLucro));
			cmd.Parameters.Add(getParameter(cmd, "@cLucro", clucroVO.cLucro));
			cmd.Parameters.Add(getParameter(cmd, "@idFilial", clucroVO.idFilial));
            if (clucroVO.idCLucroPai != -1 && !String.IsNullOrEmpty(Convert.ToString(clucroVO.idCLucroPai)))
                cmd.Parameters.Add(getParameter(cmd, "@idCLucroPai", clucroVO.idCLucroPai));
            else
                cmd.Parameters.Add(getParameter(cmd, "@idCLucroPai", System.DBNull.Value));
			cmd.Parameters.Add(getParameter(cmd, "@cadastro", clucroVO.cadastro));
			cmd.Parameters.Add(getParameter(cmd, "@cadastradoPor", clucroVO.cadastradoPor));
			cmd.Parameters.Add(getParameter(cmd, "@edicao", clucroVO.edicao));
			cmd.Parameters.Add(getParameter(cmd, "@editadoPor", clucroVO.editadoPor));

			try
			{
				cmd.ExecuteNonQuery();
			}
			catch (Exception ex) { throw ex; }

		}

		public long newClucro(ClucroVO clucroVO)
		{
			StringBuilder query = new StringBuilder();

			query.AppendLine("INSERT INTO clucro ");
			query.AppendLine("  ( cLucro, idFilial, idCLucroPai, cadastro, cadastradoPor, edicao, editadoPor ) ");
			query.AppendLine("VALUES ( @cLucro, @idFilial, @idCLucroPai, @cadastro, @cadastradoPor, @edicao, @editadoPor ) ");

			DbCommand cmd = this.getCommand();
			cmd.CommandText = query.ToString();

			cmd.Parameters.Add(getParameter(cmd, "@cLucro", clucroVO.cLucro));
			cmd.Parameters.Add(getParameter(cmd, "@idFilial", clucroVO.idFilial));
            if (clucroVO.idCLucroPai != -1 && !String.IsNullOrEmpty(Convert.ToString(clucroVO.idCLucroPai)))
                cmd.Parameters.Add(getParameter(cmd, "@idCLucroPai", clucroVO.idCLucroPai));
            else
                cmd.Parameters.Add(getParameter(cmd, "@idCLucroPai", System.DBNull.Value));
			cmd.Parameters.Add(getParameter(cmd, "@cadastro", clucroVO.cadastro));
			cmd.Parameters.Add(getParameter(cmd, "@cadastradoPor", clucroVO.cadastradoPor));
			cmd.Parameters.Add(getParameter(cmd, "@edicao", clucroVO.edicao));
			cmd.Parameters.Add(getParameter(cmd, "@editadoPor", clucroVO.editadoPor));


			try
			{
				cmd.ExecuteNonQuery();

				long newID = 0;
				if (tipo.Equals(DBType.MSSql))
				{
					cmd.CommandText = "SELECT IDENT_CURRENT('clucro') AS 'Identity' ";
					DbDataReader reader = cmd.ExecuteReader();
					reader.Read();
					newID = (long)reader.GetDecimal(reader.GetOrdinal("Identity"));
					reader.Close();
				}
				else if (tipo.Equals(DBType.MySql))
				{
					newID = ((MySql.Data.MySqlClient.MySqlCommand)cmd).LastInsertedId;
				}
				else if (tipo.Equals(DBType.ODBC))
				{
					//TODO implementar
				}
				else if (tipo.Equals(DBType.ORACLE))
				{
					//TODO implementar
				}
				return newID;
			}
			catch (Exception ex) { throw ex; }

		}

		public void delClucro(ClucroVO clucroVO)
		{
			StringBuilder query = new StringBuilder();

			query.AppendLine("DELETE FROM clucro ");
			query.AppendLine("WHERE idCLucro=@idCLucro ");

			DbCommand cmd = getCommand();
			cmd.CommandText = query.ToString();

			cmd.Parameters.Add(getParameter(cmd, "@idCLucro", clucroVO.idCLucro));


			try
			{
				cmd.ExecuteNonQuery();
			}
			catch (Exception ex) { throw ex; }

		}
		public int getCountClucroxIdFilial(int? idFilial)
		{
			StringBuilder query = new StringBuilder();

			query.AppendLine("SELECT COUNT(idFilial) as ocorrencias FROM clucro WHERE IdFilial=@IdFilial ");

			DbCommand cmd = this.getCommand();
			cmd.CommandText = query.ToString();

			cmd.Parameters.Add(getParameter(cmd, "@IdFilial", idFilial));

			DbDataReader reader = null;
			try
			{
				reader = cmd.ExecuteReader();
				reader.Read();

				int ocorrencias = 0;
				ocorrencias = reader.GetInt32(reader.GetOrdinal("ocorrencias"));
				reader.Close();
				return ocorrencias;
			}
			catch (Exception ex) { throw ex; }
			finally
			{
				if (reader != null && !reader.IsClosed)
					reader.Close();
			}
		}
		public int getCountClucroxIdCLucroPai(int? idCLucroPai)
		{
			StringBuilder query = new StringBuilder();

			query.AppendLine("SELECT COUNT(idCLucroPai) as ocorrencias FROM clucro WHERE IdCLucroPai=@IdCLucroPai ");

			DbCommand cmd = this.getCommand();
			cmd.CommandText = query.ToString();

			cmd.Parameters.Add(getParameter(cmd, "@IdCLucroPai", idCLucroPai));

			DbDataReader reader = null;
			try
			{
				reader = cmd.ExecuteReader();
				reader.Read();

				int ocorrencias = 0;
				ocorrencias = reader.GetInt32(reader.GetOrdinal("ocorrencias"));
				reader.Close();
				return ocorrencias;
			}
			catch (Exception ex) { throw ex; }
			finally
			{
				if (reader != null && !reader.IsClosed)
					reader.Close();
			}
		}
	}
}
